"""
数据访问层
"""
from typing import List, Optional, Dict, Any
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import and_, or_, desc, asc, select
from sqlalchemy.orm import selectinload
from database.models import GroupInfo, Message, UserProfile, SystemLog, Comment,Task
from database.connection import db_manager
from datetime import datetime, timedelta
import logging

logger = logging.getLogger(__name__)


class UserProfileRepository:
    """用户档案数据访问层"""
    
    def __init__(self, db: AsyncSession):
        self.db = db
    
    async def create(self, profile_data: Dict[str, Any]) -> UserProfile:
        """创建用户档案"""
        profile = UserProfile(**profile_data)
        self.db.add(profile)
        await self.db.commit()
        await self.db.refresh(profile)
        return profile

    async def create_many(self, profiles_data: List[Dict[str, Any]]) -> List[UserProfile]:
        """批量创建用户档案"""
        try:
            logger.info(f"开始创建 {len(profiles_data)} 个用户档案")
            logger.info(f"示例数据: {profiles_data[0] if profiles_data else 'None'}")
            
            # 检查当前数据库连接状态
            result = await self.db.execute(select(1))
            print(f"数据库连接测试成功: {result.scalar()}")
            
            
            profiles = [UserProfile(**data) for data in profiles_data]
            print(f"创建了 {len(profiles)} 个UserProfile对象")
            
            self.db.add_all(profiles)
            print("已调用add_all")
            
            # 提交前先flush，以便捕获任何数据库错误
            await self.db.flush()
            print("数据已flush到数据库")
            
            # 检查flush后的ID
            for i, profile in enumerate(profiles):
                print(f"Profile {i}: name={profile.name}, id={profile.id}")
            
            await self.db.commit()
            print("事务已提交")
            
            # 强制刷新所有对象
            for profile in profiles:
                await self.db.refresh(profile)
                print(f"刷新后 Profile ID: {profile.id}, Name: {profile.name}")
            return profiles
            
        except Exception as e:
            logger.error(f"批量创建用户档案失败: {str(e)}")
            try:
                await self.db.rollback()
                logger.info("已回滚事务")
            except Exception as rollback_e:
                logger.error(f"回滚失败: {str(rollback_e)}")
            raise
    
    async def get_by_id(self, profile_id: int) -> Optional[UserProfile]:
        """根据ID获取用户档案"""
        result = await self.db.execute(
            select(UserProfile).where(UserProfile.id == profile_id)
        )
        return result.scalar_one_or_none()
    
    async def get_by_platform_id(self, platform: str, platform_id: str) -> Optional[UserProfile]:
        """根据平台和平台ID获取用户档案"""
        result = await self.db.execute(
            select(UserProfile).where(
                and_(
                    UserProfile.platform == platform,
                    UserProfile.profile_id == platform_id
                )
            )
        )
        return result.scalar_one_or_none()
    
    async def update(self, profile_id: int, update_data: Dict[str, Any]) -> Optional[UserProfile]:
        """更新用户档案"""
        profile = await self.get_by_id(profile_id)
        if not profile:
            return None
        
        for key, value in update_data.items():
            if hasattr(profile, key):
                setattr(profile, key, value)
        
        profile.updated_at = datetime.utcnow()
        await self.db.commit()
        await self.db.refresh(profile)
        return profile
    
    async def delete(self, profile_id: int) -> bool:
        """删除用户档案"""
        profile = await self.get_by_id(profile_id)
        if not profile:
            return False
        
        await self.db.delete(profile)
        await self.db.commit()
        return True
    
    async def search(self, platform: str = None, keyword: str = None, 
               limit: int = 100, offset: int = 0,account_name: str = None,is_add_friend: int = None) -> List[UserProfile]:
        """搜索用户档案"""
        query = select(UserProfile)
        
        if platform:
            query = query.where(UserProfile.platform == platform)
        
        if account_name:
            query = query.where(UserProfile.account == account_name)
        if is_add_friend is not None:
            query = query.where(UserProfile.is_add_friend == is_add_friend)
        if keyword:
            query = query.where(
                or_(
                    UserProfile.username.contains(keyword),
                    UserProfile.full_name.contains(keyword),
                    UserProfile.headline.contains(keyword),
                    UserProfile.company.contains(keyword)
                )
            )
        
        query = query.order_by(desc(UserProfile.created_at)).offset(offset).limit(limit)
        result = await self.db.execute(query)
        return result.scalars().all()
    
    async def get_recent_profiles(self, platform: str = None, days: int = 7) -> List[UserProfile]:
        """获取最近采集的用户档案"""
        since_date = datetime.utcnow() - timedelta(days=days)
        query = select(UserProfile).where(UserProfile.created_at >= since_date)
        
        if platform:
            query = query.where(UserProfile.platform == platform)
        
        query = query.order_by(desc(UserProfile.created_at))
        result = await self.db.execute(query)
        return result.scalars().all()


class TaskRepository:
    """任务表数据访问层"""

    def __init__(self, db: AsyncSession):
        self.db = db

    async def create(self, task_data: Dict[str, Any]):
        """创建任务"""
        from database.models import Task
        task = Task(**task_data)
        self.db.add(task)
        await self.db.commit()
        await self.db.refresh(task)
        return task

    async def get_by_task_id(self, task_id: str):
        """根据任务ID获取任务"""
        from database.models import Task
        result = await self.db.execute(
            select(Task).where(Task.task_id == task_id)
        )
        return result.scalar_one_or_none()

    async def update_status(self, task_id: str, status: str, results: Dict[str, Any] = None):
        """更新任务状态"""
        from database.models import Task
        try:
            task = await self.get_by_task_id(task_id)
            if not task:
                return None
            
            # 验证status字段长度
            if len(status) > 20:
                raise ValueError(f"状态值过长: {len(status)}字符，最大允许20字符")
            
            task.status = status
            if results is not None:
                task.results = results
            task.updated_at = datetime.utcnow()
            
            await self.db.commit()
            await self.db.refresh(task)
            return task
            
        except Exception as e:
            await self.db.rollback()
            # 记录详细错误信息
            import json
            results_size = 0
            if results:
                try:
                    results_size = len(json.dumps(results, ensure_ascii=False))
                except:
                    results_size = len(str(results))
            
            error_msg = f"更新任务状态失败 - 任务ID: {task_id}, 状态: {status}, 结果数据大小: {results_size}字节, 错误: {str(e)}"
            raise Exception(error_msg) from e

    async def get_by_status(self, status: str) -> list:
        """根据状态获取任务"""
        from database.models import Task
        result = await self.db.execute(
            select(Task).where(Task.status == status).order_by(desc(Task.created_at))
        )
        return result.scalars().all()

    async def get_recent_tasks(self, limit: int = 50) -> list:
        """获取最近的任务"""
        from database.models import Task
        result = await self.db.execute(
            select(Task).order_by(desc(Task.created_at)).limit(limit)
        )
        return result.scalars().all()



class SystemLogRepository:
    """系统日志数据访问层"""
    
    def __init__(self, db: AsyncSession):
        self.db = db
    
    async def create(self, log_data: Dict[str, Any]) -> SystemLog:
        """创建系统日志"""
        print(f"SystemLogRepository.create 开始，数据: {log_data}")
        try:
            log = SystemLog(**log_data)
            print("SystemLog 对象创建成功")
            self.db.add(log)
            print("添加到数据库会话成功")
            await self.db.commit()
            print("数据库提交成功")
            await self.db.refresh(log)
            print(f"数据库刷新成功，ID: {log.id}")
            return log
        except Exception as e:
            print(f"SystemLogRepository.create 失败: {e}")
            import traceback
            traceback.print_exc()
            raise
    
    async def get_by_task_id(self, task_id: str) -> List[SystemLog]:
        """根据任务ID获取日志"""
        result = await self.db.execute(
            select(SystemLog).where(
                SystemLog.task_id == task_id
            ).order_by(asc(SystemLog.created_at))
        )
        return result.scalars().all()
    
    async def get_recent_logs(self, level: str = None, limit: int = 100) -> List[SystemLog]:
        """获取最近的日志"""
        query = select(SystemLog)
        
        if level:
            query = query.where(SystemLog.level == level)
        
        query = query.order_by(desc(SystemLog.created_at)).limit(limit)
        result = await self.db.execute(query)
        return result.scalars().all()


class CommentRepository:
    """评论数据访问层"""

    def __init__(self, db: AsyncSession):
        self.db = db

    async def create(self, comment_data: Dict[str, Any]) -> Comment:
        """创建评论"""
        from database.models import Comment  # 局部导入以避免循环依赖
        comment = Comment(**comment_data)
        self.db.add(comment)
        await self.db.commit()
        await self.db.refresh(comment)
        return comment

    async def create_many(self, comments_data: List[Dict[str, Any]]) -> List[Comment]:
        """批量创建评论"""
        from database.models import Comment
        comments = [Comment(**data) for data in comments_data]
        self.db.add_all(comments)
        await self.db.commit()
        for comment in comments:
            await self.db.refresh(comment)
        return comments

    async def get_by_id(self, comment_id: int) -> Comment:
        """根据ID获取评论"""
        from database.models import Comment
        result = await self.db.execute(
            select(Comment).where(Comment.id == comment_id)
        )
        return result.scalar_one_or_none()

    async def get_by_platform(self, platform: str, limit: int = 100) -> List[Comment]:
        """根据平台获取评论"""
        from database.models import Comment
        result = await self.db.execute(
            select(Comment)
            .where(Comment.platform == platform)
            .order_by(desc(Comment.created_at))
            .limit(limit)
        )
        return result.scalars().all()

    async def get_recent_comments(self, limit: int = 100) -> List[Comment]:
        """获取最近的评论"""
        from database.models import Comment
        result = await self.db.execute(
            select(Comment)
            .order_by(desc(Comment.created_at))
            .limit(limit)
        )
        return result.scalars().all()

    async def get_by_link(self, link: str) -> List[Comment]:
        """根据原始链接获取评论"""
        from database.models import Comment
        result = await self.db.execute(
            select(Comment)
            .where(Comment.link == link)
            .order_by(desc(Comment.created_at))
        )
        return result.scalars().all()

    async def get_by_commenter(self, comment_name: str, limit: int = 100) -> List[Comment]:
        """根据评论者名称获取评论"""
        from database.models import Comment
        result = await self.db.execute(
            select(Comment)
            .where(Comment.comment_name == comment_name)
            .order_by(desc(Comment.created_at))
            .limit(limit)
        )
        return result.scalars().all()


class MessageRepository:
    """消息数据访问层"""

    def __init__(self, db: AsyncSession):
        self.db = db

    async def create(self, message_data: Dict[str, Any]):
        """创建消息"""
        from database.models import Message
        message = Message(**message_data)
        self.db.add(message)
        await self.db.commit()
        await self.db.refresh(message)
        return message

    async def get_by_id(self, message_id: int) -> Optional[Message]:
        """根据ID获取消息"""
        from database.models import Message
        result = await self.db.execute(
            select(Message).where(Message.id == message_id)
        )
        return result.scalar_one_or_none()

    async def get_by_sender(self, sender: str, limit: int = 100) -> List[Message]:
        """根据发送人获取消息"""
        from database.models import Message
        result = await self.db.execute(
            select(Message)
            .where(Message.sender == sender)
            .order_by(desc(Message.sent_at))
            .limit(limit)
        )
        return result.scalars().all()

    async def get_by_receiver(self, receiver: str, limit: int = 100) -> List[Message]:
        """根据接收人获取消息"""
        from database.models import Message
        result = await self.db.execute(
            select(Message)
            .where(Message.receiver == receiver)
            .order_by(desc(Message.sent_at))
            .limit(limit)
        )
        return result.scalars().all()

    async def get_unsent_messages(self, limit: int = 100) -> List[Message]:
        """获取未发送的消息"""
        from database.models import Message
        result = await self.db.execute(
            select(Message)
            .where(Message.is_sent == False)
            .order_by(asc(Message.created_at))
            .limit(limit)
        )
        return result.scalars().all()

    async def update_message_status(self, message_id: int, is_sent: bool = True) -> Optional[Message]:
        """更新消息发送状态"""
        from database.models import Message
        message = await self.get_by_id(message_id)
        if not message:
            return None

        message.is_sent = is_sent
        message.sent_at = datetime.utcnow() if is_sent and message.sent_at is None else message.sent_at
        await self.db.commit()
        await self.db.refresh(message)
        return message

    async def update(self, message_id: int, update_data: Dict[str, Any]) -> Optional[Message]:
        """更新消息"""
        from database.models import Message
        message = await self.get_by_id(message_id)
        if not message:
            return None

        for key, value in update_data.items():
            if hasattr(message, key):
                setattr(message, key, value)

        await self.db.commit()
        await self.db.refresh(message)
        return message

    async def get_recent_messages(self, limit: int = 100) -> List[Message]:
        """获取最近的消息"""
        from database.models import Message
        result = await self.db.execute(
            select(Message)
            .order_by(desc(Message.created_at))
            .limit(limit)
        )
        return result.scalars().all()
    

class GroupInfoRepository:
    """小组信息数据访问层"""

    def __init__(self, db: AsyncSession):
        self.db = db

    async def create(self, group_data: Dict[str, Any]) -> GroupInfo:
        """创建小组信息"""
        from database.models import GroupInfo
        group_info = GroupInfo(**group_data)
        self.db.add(group_info)
        await self.db.commit()
        await self.db.refresh(group_info)
        return group_info

    async def create_many(self, groups_data: List[Dict[str, Any]]) -> List[GroupInfo]:
        """批量创建小组信息"""
        from database.models import GroupInfo
        groups = [GroupInfo(**data) for data in groups_data]
        self.db.add_all(groups)
        await self.db.commit()
        for group in groups:
            await self.db.refresh(group)
        return groups

    async def get_by_id(self, group_id: int) -> Optional[GroupInfo]:
        """根据ID获取小组信息"""
        from database.models import GroupInfo
        result = await self.db.execute(
            select(GroupInfo).where(GroupInfo.id == group_id)
        )
        return result.scalar_one_or_none()

    async def get_by_group_name(self, group_name: str, limit: int = 100) -> List[GroupInfo]:
        """根据组名获取小组信息"""
        from database.models import GroupInfo
        result = await self.db.execute(
            select(GroupInfo)
            .where(GroupInfo.group_name == group_name)
            .order_by(desc(GroupInfo.created_at))
            .limit(limit)
        )
        return result.scalars().all()

    async def get_by_group_url(self, group_url: str) -> Optional[GroupInfo]:
        """根据组链接获取小组信息"""
        from database.models import GroupInfo
        result = await self.db.execute(
            select(GroupInfo).where(GroupInfo.group_url == group_url)
        )
        return result.scalar_one_or_none()

    async def get_recent_groups(self, limit: int = 100) -> List[GroupInfo]:
        """获取最近的小组信息"""
        from database.models import GroupInfo
        result = await self.db.execute(
            select(GroupInfo)
            .order_by(desc(GroupInfo.created_at))
            .limit(limit)
        )
        return result.scalars().all()

    async def get_groups_by_filter(self, 
                                 group_name: Optional[str] = None,
                                 min_member_count: Optional[int] = None,
                                 max_member_count: Optional[int] = None,
                                 min_daily_post_count: Optional[int] = None,
                                 max_daily_post_count: Optional[int] = None,
                                 is_joined: Optional[int] = None,
                                 limit: int = 100) -> List[GroupInfo]:
        """根据多个字段筛选小组信息"""
        from database.models import GroupInfo
        query = select(GroupInfo)
        
        if group_name:
            query = query.where(GroupInfo.group_name.ilike(f"%{group_name}%"))
        
        if min_member_count is not None:
            query = query.where(GroupInfo.member_count >= min_member_count)
        
        if max_member_count is not None:
            query = query.where(GroupInfo.member_count <= max_member_count)
        
        if min_daily_post_count is not None:
            query = query.where(GroupInfo.daily_post_count >= min_daily_post_count)
        
        if max_daily_post_count is not None:
            query = query.where(GroupInfo.daily_post_count <= max_daily_post_count)
        
        if is_joined is not None:
            query = query.where(GroupInfo.is_joined == is_joined)
        
        query = query.order_by(desc(GroupInfo.created_at)).limit(limit)
        result = await self.db.execute(query)
        return result.scalars().all()
    
    
    
    
    async def update(self, group_id: int, update_data: Dict[str, Any]) -> Optional[GroupInfo]:
        """更新小组信息"""
        from database.models import GroupInfo
        group_info = await self.get_by_id(group_id)
        if not group_info:
            return None

        for key, value in update_data.items():
            if hasattr(group_info, key):
                setattr(group_info, key, value)

        await self.db.commit()
        await self.db.refresh(group_info)
        return group_info

    async def delete(self, group_id: int) -> bool:
        """删除小组信息"""
        from database.models import GroupInfo
        group_info = await self.get_by_id(group_id)
        if not group_info:
            return False

        await self.db.delete(group_info)
        await self.db.commit()
        return True